package tsli.acm.dao;

import java.util.List;
import java.util.Map;

import tsli.acm.database.Database;

public class TempAlBonusDetailDAO {
	
	private Database db;

	public TempAlBonusDetailDAO(Database db) {
		this.db = db;
	}
	
	public boolean InsertValues(List<Map<String, Object>> rows,String yyyymm){
				
		try{
			String sql = "";
			for(Map<String, Object> row:rows){
				sql = " INSERT INTO TSLI_TEMP_AL_BONUS_DETAIL ( CLOS_YM, PE_NO_SM, RECAL_YM,BONUS_NET, BONUS_FL,PC_OLD,PC_NEW,OLD_ACTIVE_AMOUNT,NEW_ACTIVE_AMOUNT) \n"
					+ "  VALUES ('"+ row.get("CLOS_YM")+"' ,"
							+ " '"+row.get("PE_NO_SM")+"' ,"
							+ " ? ,"
							+ " "+row.get("BONUS_NET")+" ,"
							+ " "+row.get("BONUS_FL")+" ,"
							+ " "+row.get("PC_OLD")+","
							+ " "+row.get("PC_NEW")+","
							+ " "+row.get("OLD_ACTIVE_AMOUNT")+","
							+ " "+row.get("NEW_ACTIVE_AMOUNT")+" )";  
				
//				System.out.println(sql);
				
				this.db.insert(sql,yyyymm);
			}
		}
		catch(Exception e){
			System.out.println("Error : "+e);
			return false;
		}
		return true;
	}
	
	public Map<String,Object> getBonusDetailOfPeNoSm(String penosm,String closYm){
//		String vaSql = ""+
//				"	select a.*,DIF_BONUS_NET_BONUS_FL,(a.BONUS_NET - DIF_BONUS_NET_BONUS_FL) as TOTAL_BONUS 	 "+
//				"	from TSLI_TEMP_AL_BONUS_DETAIL a,                                                            "+
//				"	(                                                                                            "+
//				"	select sum(b.BONUS_NET)-sum(b.BONUS_FL) as DIF_BONUS_NET_BONUS_FL                            "+
//				"	from TSLI_TEMP_AL_BONUS_DETAIL b                                                             "+
//				"	where b.PE_NO_SM = "+penosm+"                                                                "+
//				"	and RECAL_YM = "+closYm+"      								           						"+ 
//				"	)                                                                                            "+
//				"	where a.clos_ym = "+closYm+"                                                                 "+
//				"	and a.PE_NO_SM = "+penosm+"                                                                  ";
		
		String vaSql = "select a.*,c.DIF_BONUS_NET_BONUS_FL,(a.BONUS_NET - c.DIF_BONUS_NET_BONUS_FL) as TOTAL_BONUS  " +
		        "   ,b.NM_TITLE_THAI||b.NM_THAI||'   '||b.SURNM_THAI as NAME 	                                     " +
				" 			from TSLI_TEMP_AL_BONUS_DETAIL a LEFT OUTER JOIN TSLI_GA_AGENT_STRUCTURE b           " +
		        "   ON a.PE_NO_SM = b.PE_NO_SM and a.CLOS_YM = b.CLOS_YM,                                        " +
				" 			(                                                                                    " +        
				" 			select sum(b.BONUS_NET)-sum(b.BONUS_FL) as DIF_BONUS_NET_BONUS_FL                    " +        
				" 			from TSLI_TEMP_AL_BONUS_DETAIL b                                                     " +        
				" 			where b.PE_NO_SM = "+penosm+"                                                        " +       
				" 			and RECAL_YM = "+closYm+"  								           					" 	+
				" 			) c                                                                                  " +          
				" 			where a.clos_ym = "+closYm+"                                                             " +
				" 			and a.PE_NO_SM = "+penosm+"                                                           " +
		        "   and a.RECAL_YM = "+closYm+"                                                                    " +
		        "   and b.clos_ym = "+closYm+"                                                                     " +
		        "   and b.DTY_CD = 'S10240203'                                                                   " ;
				
		Map<String, Object> result = db.querySingle(vaSql);
		if (result != null) {
			return result;
		} else {
			return null;
		}		
	}
	
	public double getRemainingMOV (String penosm,String yyyymm){
		
		String vaSql = ""+
				"		select (a.BONUS_NET - DIF_BONUS_NET_BONUS_FL) as CUR_BONUS	 			\n"+
				"		from TSLI_TEMP_AL_BONUS_DETAIL a,                                       \n"+                   
				"		(                                                                       \n"+                   
				"		select sum(b.BONUS_NET)-sum(b.BONUS_FL) as DIF_BONUS_NET_BONUS_FL       \n"+                   
				"		from TSLI_TEMP_AL_BONUS_DETAIL b                                        \n"+                   
				"		where b.PE_NO_SM = 221201224                                            \n"+                
				"		and RECAL_YM = 201403     								           		\n"+		
				"		)                                                                       \n"+                   
				"		where a.clos_ym = "+yyyymm+" --yyyymm                                   \n"+                   
				"		and a.PE_NO_SM = "+penosm+"  --pe_no_sm                                 \n";       
				
		Map<String, Object> result = db.querySingle(vaSql);
		if (result != null) {
			return Double.parseDouble(result.get("CUR_BONUS").toString());
		} else {
			return 0;
		}		
	}
	
	public boolean isAlreadyCalculateAlBonus(String closYm){
		
		String vaSql = ""+
				"	select count(*) as bonus_row		   		\n"+
				"	from TSLI_TEMP_AL_BONUS_DETAIL        	 	\n"+
				"	where recal_ym = "+closYm+" ---clos_ym   	\n";
		
		Map<String, Object> result = db.querySingle(vaSql);
		
		if(Integer.parseInt(result.get("BONUS_ROW").toString())>0){
			return true; //It is already calculateAlBonus
		}
		else{
			return false;//Not calculate AL bonus yet
		}
	}
	
	

}
